package web.rec;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DBTableTool {
	private Connection conn;
	private Map<String,DBTableData> dbTableDataMap = new HashMap<String,DBTableData>();
	private Map<String,String> fkey_pkey_map = new HashMap<String,String>();
	private Set<String> mainPrimaryKeySet = new HashSet<String>();
	
	public DBTableTool(Connection conn, String pkey_table){
		this.conn = conn;
		ResultSet rs = null;
		
		try{
			DatabaseMetaData meta = conn.getMetaData();
			
			ArrayList<String> tableStringList = new ArrayList<String>();
			tableStringList.add(pkey_table);
			String curr_table;
			boolean first = true;
			
			
			while( !tableStringList.isEmpty()){
				curr_table = tableStringList.remove(0);
				
				DBTableData tableData = dbTableDataMap.get(curr_table);
				if(tableData == null){
					tableData = new DBTableData();
				}else{
					continue;
				}
				
				
				rs = meta.getColumns(null, null, curr_table, null);
				while (rs.next()) {
					String colName = rs.getString("COLUMN_NAME");
					tableData.addColumn(colName);
				}
				
				rs = meta.getPrimaryKeys(null, null, curr_table);
				while (rs.next()) {
					String pkey = rs.getString("COLUMN_NAME") + " " + curr_table;
					if(first){
						mainPrimaryKeySet.add(pkey);
					} 
					
					tableData.addPkey(pkey);
				}
				
				System.out.println("table: " + curr_table );
				System.out.println("columns: " + tableData.getColumnList() );
				System.out.println("pkeys: " + tableData.getPkeyList() );
				
				System.out.println();
				
				
				boolean fkey_maps_to_pkey = false;
				for(String key: tableData.getPkeyList() ){
					if(mapsToMainKey(key))
						fkey_maps_to_pkey = true;
				}
				if(!fkey_maps_to_pkey && !first){
					continue;
				}
				
				rs = meta.getExportedKeys(conn.getCatalog(), null, curr_table);
				while (rs.next()) {
					String fkTableName = rs.getString("FKTABLE_NAME");
					String fkey =  rs.getString("FKCOLUMN_NAME") + " " + fkTableName;
					String pkey = rs.getString("PKCOLUMN_NAME") + " " + curr_table;
					

					
					tableStringList.add(fkTableName);
					fkey_pkey_map.put(fkey, pkey);
					
					//tableData.pkeyList.add(pkey);
					
					
				}
	
				dbTableDataMap.put(curr_table, tableData);
				
				first = false;
				
			}
			
			
			
		}catch(Exception e){
			e.printStackTrace();
		}

	}
	
	public boolean mapsToMainKey(String fkey){
		String curr_key = fkey;
		String prev_key;
		do{
			prev_key = curr_key;
			curr_key = fkey_pkey_map.get(prev_key);
			
		}while(curr_key != null);
		
		return mainPrimaryKeySet.contains(prev_key);
		
	}
	
	
	public DBTableData getTableData(String table){
		return dbTableDataMap.get(table);
	}
	
	public List<String> getTableColumns(Set<String> tables){
		List<String> colList = new ArrayList<String>();
		if(dbTableDataMap.isEmpty()){
			return null;
		}
		
		for(String t : tables){
			
			try{
				List<String> tmp = dbTableDataMap.get(t).getColumnList();
				for(String col: tmp){
					if(!colList.contains(col)){
						colList.add(col);
					}
				}
				
				//colSet.addAll( dbTableDataMap.get(t).columnList );
			}catch(Exception e){
				System.out.println("table " + t + " does not exist");
			}
		}
		return colList;
		
	}
	
	public static void main(String[] args){
		Connection conn = ResourceTool.getConnection();
		
		DBTableTool dbtable = new DBTableTool(conn, "Student");
	}
	
}


class DBTableData {
	private List<String> columnList = new ArrayList<String>();
	private List<String> pkeyList = new ArrayList<String>();
	
	public boolean addPkey(String pkey){
		if(pkeyList.contains(pkey)){
			return false;
		}
		return pkeyList.add(pkey);
	}
	
	public boolean addColumn(String col){
		if(columnList.contains(col)){
			return false;
		}
		return columnList.add(col);
	}
	
	public List<String> getColumnList(){
		return columnList;
	}
	public List<String> getPkeyList(){
		return pkeyList;
	}
}
